const express = require('express');

const routes = express.Router();

//引入mysql
const mysql = require('mysql');
//创建连接池
var pool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'root',
    password : 'root',
    database : 'stumsg'
})
routes.get('/',(req,resp)=>{

    //获取cookies
    var uid = req.cookies.uid;
    var gender = req.cookies.gender;

    //判断
    if(gender == 1){
        var pname = '教师';
    }else {
        var pname = '学生';

    }
    //每一页把条数据
    var param = 8;
    //将当前的页码传递出去作比较
    //如果首次登录教师账号  params 为空的情况下让它自动生成一个值 然后直接与下方list进行数学运算 默认查询第一页的数据
    var pos = (req.query.params) ? req.query.params : 1;
    //console.log(pos)
    //获取查询的页条数的起始位置
    var list = (pos * param) - param;
    // console.log(list)
    //    执行sql语句
    var sql = `SELECT sub_list.* , stu_list.stu_name FROM sub_list LEFT JOIN stu_list ON sub_list.log_uid = stu_list.uid WHERE post = 2 ORDER BY sub_list.id DESC LIMIT ${list}, ${param}`;
    //查询总条数
    var sqll = `SELECT COUNT(*) as sum FROM sub_list WHERE post = 2`;
    // console.log(sql)
    pool.getConnection(function(err,connection){
        if(err) throw err;
        //    链接成功
        connection.query(sql,(error,result)=> {
            //console.log(result)
            //    链接成功
            connection.query(sqll,(errs,res)=> {

                //    释放连接   关闭数据库
                connection.release();

                //提取出来总条数
                var sum = res[0].sum;

                //判断需要多少页  条数不能为小数点  需要向上取整
                var params = Math.ceil( sum / param );
                //console.log(params)
                if(result){
                    //
                    resp.render('home_teach',{
                        uid : uid,
                        pname : pname,
                        result : result,
                        params : params,  //总页数
                        pos : pos  //当前页数
                    })
                }

                //sql语句出错执行报错处理
                if(error) throw error;
            })
        })
    })

})

//暴露接口
module.exports = routes;